USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetProductDetail]    Script Date: 8/16/2014 4:47:18 PM ******/
DROP PROCEDURE [dbo].[GetProductDetail]
GO

/****** Object:  StoredProcedure [dbo].[GetProductDetail]    Script Date: 8/16/2014 4:47:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Nguyen Vu Nam
-- Create date: 20131219
-- Description:	Get product's Detail by unique string
-- =============================================
CREATE PROCEDURE [dbo].[GetProductDetail]
	@ParentTypeID		INT,
	@ChildTypeID		INT,
	@UniqueString		NVARCHAR(500),
	@Lang				INT = 1
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @CurrentType	INT
	DECLARE @EnCode INT = 2

	SELECT 
		   CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Title_En] ELSE [dbo].[Products].[Title] END AS [Title]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Content_En] ELSE [dbo].[Products].[Content] END AS [Content]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ProductDetail_En] ELSE [dbo].[Products].[ProductDetail] END AS [ProductDetail]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageSmallURL_En] ELSE [dbo].[Products].[ImageSmallURL] END AS [ImageSmallURL]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageLargeURL_En] ELSE [dbo].[Products].[ImageLargeURL] END AS [ImageLargeURL]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[MetaContent_En] ELSE [dbo].[Products].[MetaContent] END AS [MetaContent]
		  ,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Price_En] ELSE [dbo].[Products].[Price] END AS [Price]
		  ,[dbo].[Products].[ID]			  
		  ,[dbo].[Products].[UniqueString]
		  ,[dbo].[Products].[HomePageDisplayBlockID]
		  ,[dbo].[Products].[TypeID]
		  ,[dbo].[Products].[SupTypeID]		  
		  ,[dbo].[Products].[DeliveryDetail]		 
		  ,[dbo].[Products].[CreatedAt]
		  ,[dbo].[Products].[CreatedBy]
		  ,[dbo].[Products].[LastUpdatedAt]
		  ,[dbo].[Products].[LastUpdatedBy]		  
	FROM [dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
				ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID]
	WHERE [dbo].[Products].[UniqueString] = @UniqueString
	AND [dbo].[Products].[TypeID] = @ChildTypeID
	AND [dbo].[Products].[SupTypeID] = @ParentTypeID
	AND [dbo].[Products].[IsActive] <> 0		
	AND [dbo].[GenericTypes].[IsActive] <> 0
	
	SELECT TOP 3
		 CASE @Lang WHEN @EnCode THEN [dbo].[Products].[Title_En] ELSE [dbo].[Products].[Title] END AS [Title]
		,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageSmallURL_En] ELSE [dbo].[Products].[ImageSmallURL] END AS [ImageSmallURL]
		,CASE @Lang WHEN @EnCode THEN [dbo].[Products].[ImageLargeURL_En] ELSE [dbo].[Products].[ImageLargeURL] END AS [ImageLargeURL]		
		,[dbo].[Products].[ID]
		,[dbo].[Products].[TypeID]
		,[dbo].[Products].[SupTypeID]
		,[dbo].[Products].[UniqueString]		
	FROM [NGroup_VTL_DB].[dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
				ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID]
	WHERE 
		[dbo].[Products].[IsActive] <> 0
		AND [dbo].[Products].[UniqueString] <> @UniqueString
		AND [dbo].[Products].[TypeID] = @ChildTypeID
		AND [dbo].[Products].[SupTypeID] = @ParentTypeID
		AND [dbo].[GenericTypes].[IsActive] <> 0
	ORDER BY [dbo].[Products].[CreatedAt] DESC, [dbo].[Products].[LastUpdatedAt] DESC
END



GO


